<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
            "http://www.w3.org/TR/REC-html40/loose.dtd">
<HTML>
<HEAD>



<META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<META name="GENERATOR" content="hevea 1.08">
<LINK rel="stylesheet" type="text/css" href="embroot.css">
<TITLE>
Data Templates
</TITLE>
</HEAD>
<BODY >
<A HREF="embroot061.html"><IMG SRC ="previous_motif.gif" ALT="Previous"></A>
<A HREF="embroot059.html"><IMG SRC ="contents_motif.gif" ALT="Up"></A>
<A HREF="embroot063.html"><IMG SRC ="next_motif.gif" ALT="Next"></A>
<HR>

<H2 CLASS="section"><A NAME="htoc119">11.3</A>&nbsp;&nbsp;Data Templates</H2><UL>
<LI><A HREF="embroot062.html#toc62">Conversion between ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> and database types</A>
<LI><A HREF="embroot062.html#toc63">Specifying buffer sizes in templates</A>
</UL>

<A NAME="data-templates"></A>
If supported by the database, the interface allows the use of
prepared SQL statements with parameters (placeholders). Prepared SQL
statements are pre-parsed by the database, and can be executed more
efficiently for multiple times, with the placeholders acting like variables,
taking on different values for each execution.<BR>
<BR>
The syntax used for
prepared statements is that provided by the database, but a common syntax
is to use <CODE>?</CODE> to indicate a placeholder. For example:
<PRE CLASS="verbatim">
insert into employees (enum, ename, esalary, ejob) values (?, ?, ?, ?)
</PRE>would be used to add rows to the employees relation.<BR>
<BR>
Such an SQL statement has to be prepared before execution. It can then
be executed in batches to insert several tuples in one go. Preparation
involves parsing the SQL statement and setting up a buffer for the tuples.<BR>
<BR>
A data template is used as an example buffer. For the insert command above it
might look like:
<PRE CLASS="verbatim">
emp(1234,"some name",1000.0,'some job')
</PRE>
The argument positions correspond to the order of the placeholder in the
SQL statement.
The types of the data will be used to type-check the tuples when they
are inserted.<BR>
<BR>
The following ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> goal uses a template to create a cursor for an insert
command:
<PRE CLASS="verbatim">
SQL = "insert into employees (enum,ename,esalary,ejob) values (?,?,?,?)",
Template = emp(1234,"some name",1000.0,'some job'),
session_sql_prepare(H, Template, SQL, Cursor),
</PRE><CODE>H</CODE> is a handle to a database session, and <CODE>Cursor</CODE> is the cursor
created for the prepared statement <CODE>SQL</CODE>.<BR>
<BR>
The cursor can then be used to insert several rows into the employee table.
<PRE CLASS="verbatim">
cursor_next_execute(Cursor,emp(1001,"E.G. SMITH",1499.08,editor)),
cursor_next_execute(Cursor,emp(1002,"D.E. JONES",1499.08,journalist)),
</PRE>
Similarly for queries a data template specifies the types of the
columns retrieved. The positions of the arguments correspond to
the position of the select list items. The example template above
might be used for a query like
<PRE CLASS="verbatim">
SQL = "select enum, ename, esalary, ejob from employees where esalary &gt; 1000",
Template = emp(1234,"some name",1000.0,'some job'),
session_sql_query(H, Template, SQL, Cursor),
cursor_next_tuple(Cursor,Tuple),
% Tuple is now somthing like emp(1001,"E.G. SMITH",1499.08,editor)
</PRE>
If a structure or list appears in
one of the argument positions this stands for a general term, to
be stored or retrieved in external database format. This way one is not
limited to atomic types which have natural mappings to database types.<BR>
<BR>
<A NAME="toc62"></A>
<H3 CLASS="subsection"><A NAME="htoc120">11.3.1</A>&nbsp;&nbsp;Conversion between ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> and database types</H3>

Data is passed from ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> into the database via placeholders in
prepared SQL statements, and passed from the database to ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> via
the results tuples returned by executing SQL queries. 
The interface takes care of the conversion of data to/from ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP>
types to the external C API types, and the database then converts these to/from
the internal types of the database, as determined by the SQL statement used. 
The exact internal database types, and the exact conversion rules between
the C type and the database type is dependent on the database's API, but in
general the following should hold for ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> types:
<DL CLASS="description" COMPACT=compact><DT CLASS="dt-description">
<B>Strings and atoms</B><DD CLASS="dd-description"> are converted to C char * type. This should be used for 
 non-numeric data. Restrictions may apply depending on the SQL datatype &ndash;
 for example, non-binary string types (such as VARCHAR) does not accept
 generic binary strings, and SQL data and time types must be in the
 correct syntax &ndash; consult the database manual for the syntax for these types.
<DT CLASS="dt-description"><B>Integers and Floats</B><DD CLASS="dd-description"> are converted to C integers and doubles, which are
 then converted to the specified SQL numeric types. The numbers are passed
 to the database's C API at the maximum precision and size supported by
 the database's API. Any integers outside the range representable by the
 C API's integer type will raise an error. Note that while the number
 passed to the database is at maximum precisiion and size, the
 corresponding SQL numberic type specified by the SQL statement that
 receives the number may be smaller
 (e.g. SMALLINT). The exact behaviour in this case
 depends on the database.
<DT CLASS="dt-description"><B>General terms</B><DD CLASS="dd-description"> are converted to ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP>'s internal dbformat
 - a flat binary representation of the term, and then to an appropriate SQL
 binary type. This 
 allows ECL<SUP><I>i</I></SUP>PS<SUP><I>e</I></SUP> to store and retrieve general terms, but if it is
 required to exchange Prolog terms with external sources via the
 database, then the term should be first converted to EXDR format, and the
 EXDR string can then be passed to the database. Note that EXDR can only
 represent a subset of terms &ndash; see the Embedding and Interfacing manual
 for details.</DL>
<A NAME="toc63"></A>
<H3 CLASS="subsection"><A NAME="htoc121">11.3.2</A>&nbsp;&nbsp;Specifying buffer sizes in templates</H3>
Prolog terms, strings and atoms can have variable sizes, but when they are
passed into and out of the database, they pass through fixed size buffers
for reasons of efficiency.<BR>
<BR>
In the case of fetching data from fixed size database fields, the size of
these buffers is by default, the size of the field in the database.
In the case of variable sized fields and of placeholders, a default size
is chosen.<BR>
<BR>
Rather than taking the default it is possible to write templates that
specify a size. This is done by mentioning the size in the argument of
the template.
<DL CLASS="description" COMPACT=compact><DT CLASS="dt-description">
<B>'123'</B><DD CLASS="dd-description"> defines an atom datatype where the maximum length in
bytes is 123. The length is given as a decimal number.<BR>
<BR>
<DT CLASS="dt-description"><B>"123"</B><DD CLASS="dd-description"> defines a string datatype where the maximum length in
bytes is 123. The length is given as a decimal number.<BR>
<BR>
<DT CLASS="dt-description"><B>s(123,X)</B><DD CLASS="dd-description"> Describes any Prolog term that occupies up to 123 bytes
in external database format. Any structure whose first argument is an integer
can be used.
</DL>
For example the following two templates specify the same type of tuple
but the second one defines some sizes for the different elements in the
tuple as well.
<PRE CLASS="verbatim">
emp(1234,"name", Rules, job)
emp(1234,"10",rules(4000),'10')
</PRE>
The size information is used to define the minimum size of the buffer used
to pass data between ECLiPSe and the database. Depending on the database
and the situation (input/output, prepared/direct statements), such an 
intermediate buffer may not be used; in such cases, the buffer size will be
ignored. Otherwise, if the data is too big to fit into the buffer, an error
will be raised. <BR>
<BR>
The data in the buffer is passed to/from the database, which may have its own
size specification for the data, which is independent of the size
information specified in the template. In the case of sending data to the
database, and the data is too large for the database, the exact behaviour
is dependent on the database. In the case of receiving the data from the
database, and the data is too large for the buffer, an error will be raised.<BR>
<BR>
<HR>
<A HREF="embroot061.html"><IMG SRC ="previous_motif.gif" ALT="Previous"></A>
<A HREF="embroot059.html"><IMG SRC ="contents_motif.gif" ALT="Up"></A>
<A HREF="embroot063.html"><IMG SRC ="next_motif.gif" ALT="Next"></A>
</BODY>
</HTML>
